use std::{any::Any, collections::HashMap};

use chrono::{DateTime, Local};
use tokio::sync::oneshot;
use tokio_postgres::{types::FromSql, Client, NoTls};

use crate::types::{self, channel};

use super::table::{BLOG_TABLE_NAME, IDEA_TABLE_NAME};

async fn handle_blog_add(
    client: &Client,
    request: &types::blog::AddRequest,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    println!("handle_blog_add: {:?}", &request);
    // 添加一个新blog
    let sql = &format!(
        "INSERT INTO {} (title, content, tags, author, author_id)
        VALUES ($1, $2, $3, $4, $5) RETURNING id;",
        BLOG_TABLE_NAME
    );

    // 执行插入用户的 SQL 语句
    match client
        .query_one(
            sql,
            &[
                &request.title,
                &request.content,
                &request.tags,
                &request.author,
                &request.author_id,
            ],
        )
        .await
    {
        Ok(row) => {
            let id = row.get::<_, i32>(0) as u32;
            rsp_tx.send(channel::Response::BlogAdd(id));
        }
        Err(err) => {
            println!("handle_blog_add error: {:?}", err.to_string());
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_blog_update(
    client: &Client,
    update_info: &types::blog::UpdateRequest,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    let mut sql = format!(
        "UPDATE {} SET title = $1, content = $2, tags = $3 WHERE id = $4;",
        BLOG_TABLE_NAME
    );
    match client
        .execute(
            &sql,
            &[
                &update_info.title,
                &update_info.content,
                &update_info.tags,
                &update_info.id,
            ],
        )
        .await
    {
        Ok(_) => {
            rsp_tx.send(channel::Response::Ok());
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_blog_get(client: &Client, id: u32, rsp_tx: oneshot::Sender<channel::Response>) {
    println!("handle_blog_get: {}", id);
    match client
        .query_one(
            &format!("SELECT * FROM {} WHERE id = $1;", BLOG_TABLE_NAME),
            &[&(id as i32)],
        )
        .await
    {
        Ok(row) => {
            println!("handle_blog_get result: {:?}", row);
            // let blog = types::blog::Blog::from_sql(&rows.type_id(), &rows.into_bytes());
            // let blog = rows.try_get::<_, types::blog::Blog>(0).unwrap();
            let blog = types::blog::Blog {
                id: row.get::<_, i32>("id") as u32,
                title: row.get("title"),
                content: row.get("content"),
                tags: row.get::<_, Vec<String>>("tags"),
                author: row.get("author"),
                author_id: row.get("author_id"),
                views: row.get::<_, i32>("views") as u32,
                stars: row.get::<_, i32>("stars") as u32,
                created_at: row.get::<_, DateTime<Local>>("created_at"),
            };
            rsp_tx.send(channel::Response::BlogGet(blog));
        }
        Err(err) => {
            println!("Error: {:?}", &err);
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_blogs_get(
    client: &Client,
    request: &types::blog::GetMutipleRequest,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    let mut blogs_info = types::blog::GetMutipleResponse {
        total_count: 0,
        blog_count: 0,
        blogs: Vec::<types::blog::Blog>::new(),
    };

    println!("handle_blogs_get");

    match client
        .query_one(&format!("SELECT COUNT(id) FROM {};", BLOG_TABLE_NAME), &[])
        .await
    {
        Ok(row) => {
            blogs_info.total_count = row.get::<_, i64>("count") as u32;
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
            return;
        }
    }

    println!("handle_blogs_get, total_count: {}", blogs_info.total_count);

    let sql = &format!(
        "
      SELECT 
          id, 
          title, 
          SUBSTRING(content FROM 1 FOR 100) AS truncated_content, 
          tags,
          author,
          author_id,
          views,
          stars,
          created_at
      FROM 
          {}
      WHERE 
          id > $1
      ORDER BY 
          id ASC
      LIMIT $2;",
        BLOG_TABLE_NAME
    );

    // 执行查询
    match client
        .query(sql, &[&(request.id as i32), &(request.count as i64)])
        .await
    {
        Ok(rows) => {
            blogs_info.blog_count = rows.len() as u32;
            for row in rows.iter() {
                // let blog: types::blog::Blog = row.try_get(0).unwrap();
                let blog = types::blog::Blog {
                    id: row.get::<_, i32>("id") as u32,
                    title: row.get("title"),
                    content: row.get("truncated_content"),
                    tags: row.get::<_, Vec<String>>("tags"),
                    author: row.get("author"),
                    author_id: row.get("author_id"),
                    views: row.get::<_, i32>("views") as u32,
                    stars: row.get::<_, i32>("stars") as u32,
                    created_at: row.get::<_, DateTime<Local>>("created_at"),
                };
                println!("handle_blogs_get, blog: {:?}", &blog);
                blogs_info.blogs.push(blog);
            }

            println!("handle_blogs_get, blogs: {:?}", &blogs_info);
            rsp_tx.send(channel::Response::BlogsGet(blogs_info));
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_blog_delete(client: &Client, id: u32, rsp_tx: oneshot::Sender<channel::Response>) {
    match client
        .execute(
            &format!("DELETE FROM {} WHERE id = $1;", BLOG_TABLE_NAME),
            &[&id],
        )
        .await
    {
        Ok(count) => {
            if count == 1 {
                rsp_tx.send(channel::Response::Ok());
            } else {
                rsp_tx.send(channel::Response::Error("Invalid blog id".to_string()));
            }
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_blog_view_inc(
    client: &Client,
    id: u32,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    match client
        .execute(
            &format!(
                "UPDATE {} SET views = views + 1 WHERE id = $1 RETURNING views;",
                BLOG_TABLE_NAME
            ),
            &[&id],
        )
        .await
    {
        Ok(views) => {
            rsp_tx.send(channel::Response::Views(views as u32));
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_blog_star_inc(
    client: &Client,
    id: u32,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    match client
        .execute(
            &format!(
                "UPDATE {} SET stars = stars + 1 WHERE id = $1 RETURNING stars;",
                BLOG_TABLE_NAME
            ),
            &[&id],
        )
        .await
    {
        Ok(stars) => {
            rsp_tx.send(channel::Response::Stars(stars as u32));
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_blog_star_dec(
    client: &Client,
    id: u32,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    match client
        .execute(
            &format!(
                "UPDATE {} SET stars = stars - 1 WHERE id = $1 RETURNING stars;",
                BLOG_TABLE_NAME
            ),
            &[&id],
        )
        .await
    {
        Ok(stars) => {
            rsp_tx.send(channel::Response::Stars(stars as u32));
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_idea_add(
    client: &Client,
    request: &types::idea::AddRequest,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    // 添加一个新idea
    let sql = &format!(
        "INSERT INTO {} (title, content, tags, author, author_id)
        VALUES ($1, $2, $3, $4, $5) RETURNING id;",
        IDEA_TABLE_NAME
    );

    // 执行插入用户的 SQL 语句
    match client.execute(sql, &[&request]).await {
        Ok(id) => {
            rsp_tx.send(channel::Response::IdeaAdd(id as u32));
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_idea_update(
    client: &Client,
    update_info: &types::idea::UpdateRequest,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    let mut sql = format!(
        "UPDATE {} SET title = $1, content = $2, tags = $3 WHERE id = $4;",
        IDEA_TABLE_NAME
    );
    match client
        .execute(
            &sql,
            &[
                &update_info.title,
                &update_info.content,
                &update_info.tags,
                &update_info.id,
            ],
        )
        .await
    {
        Ok(_) => {
            rsp_tx.send(channel::Response::Ok());
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_idea_get(client: &Client, id: u32, rsp_tx: oneshot::Sender<channel::Response>) {
    match client
        .query_opt(
            &format!("SELECT * FROM {} WHERE id = $1;", IDEA_TABLE_NAME),
            &[&id],
        )
        .await
    {
        Ok(row) => {
            if let Some(row) = row {
                let idea: types::idea::Idea = row.try_get(0).unwrap();
                // let idea = types::idea::Idea {
                //     id: row.get("id"),
                //     title: row.get("title"),
                //     content: row.get("content"),
                //     tags: Vec::from_sql(, raw) row.get("tags"),
                //     level: row.get::<&str, i8>("level") as u8,
                //     birthday: row.get("birthday"),
                //     created_at: row.get("created_at"),
                // };
                rsp_tx.send(channel::Response::IdeaGet(idea));
            } else {
                rsp_tx.send(channel::Response::Error("Invalid idea id".to_string()));
            }
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_ideas_get(
    client: &Client,
    request: &types::idea::GetMutipleRequest,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    let mut ideas_info = types::idea::GetMutipleResponse {
        total_count: 0,
        idea_count: 0,
        ideas: Vec::new(),
    };

    match client
        .query(&format!("SELECT COUNT(id) FROM {};", IDEA_TABLE_NAME), &[])
        .await
    {
        Ok(rows) => {
            ideas_info.total_count = rows.len() as u32;
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
            return;
        }
    }

    let sql = &format!(
        "
      SELECT 
          id, 
          title, 
          SUBSTRING(content FROM 1 FOR 100) AS truncated_content, 
          tags
      FROM 
          {}
      WHERE 
          id >= $1
      ORDER BY 
          id ASC
      LIMIT $2;",
        IDEA_TABLE_NAME
    );

    // 执行查询
    match client.query(sql, &[&request.id, &request.count]).await {
        Ok(rows) => {
            ideas_info.idea_count = rows.len() as u32;
            for row in rows.iter() {
                let idea: types::idea::Idea = row.try_get(0).unwrap();
                ideas_info.ideas.push(idea);
            }
            rsp_tx.send(channel::Response::IdeasGet(ideas_info));
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_idea_delete(client: &Client, id: u32, rsp_tx: oneshot::Sender<channel::Response>) {
    match client
        .execute(
            &format!("DELETE FROM {} WHERE id = $1;", IDEA_TABLE_NAME),
            &[&id],
        )
        .await
    {
        Ok(count) => {
            if count == 1 {
                rsp_tx.send(channel::Response::Ok());
            } else {
                rsp_tx.send(channel::Response::Error("Invalid idea id".to_string()));
            }
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_idea_view_inc(
    client: &Client,
    id: u32,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    match client
        .execute(
            &format!(
                "UPDATE {} SET views = views + 1 WHERE id = $1 RETURNING views;",
                IDEA_TABLE_NAME
            ),
            &[&id],
        )
        .await
    {
        Ok(views) => {
            rsp_tx.send(channel::Response::Views(views as u32));
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_idea_star_inc(
    client: &Client,
    id: u32,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    match client
        .execute(
            &format!(
                "UPDATE {} SET stars = stars + 1 WHERE id = $1 RETURNING stars;",
                IDEA_TABLE_NAME
            ),
            &[&id],
        )
        .await
    {
        Ok(stars) => {
            rsp_tx.send(channel::Response::Stars(stars as u32));
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

async fn handle_idea_star_dec(
    client: &Client,
    id: u32,
    rsp_tx: oneshot::Sender<channel::Response>,
) {
    match client
        .execute(
            &format!(
                "UPDATE {} SET stars = stars - 1 WHERE id = $1 RETURNING stars;",
                IDEA_TABLE_NAME
            ),
            &[&id],
        )
        .await
    {
        Ok(stars) => {
            rsp_tx.send(channel::Response::Stars(stars as u32));
        }
        Err(err) => {
            rsp_tx.send(channel::Response::Error(format!("{:?}", err)));
        }
    }
}

pub async fn start(
    mut rx: tokio::sync::mpsc::Receiver<(channel::Request, oneshot::Sender<channel::Response>)>,
) {
    let (client, conn) = tokio_postgres::connect(
        "host=postgres user=postgres password=admin@postgres dbname=pudding1201",
        NoTls,
    )
    .await
    .expect("Error connecting to the database");

    // The connection object performs the actual communication with the database,
    // so spawn it off to run on its own.
    tokio::spawn(async move {
        if let Err(e) = conn.await {
            eprintln!("Error: {}", e);
        }
    });

    super::table::tables_init(&client).await.unwrap();

    // Handle request operations
    while let Some((request, rsp_tx)) = rx.recv().await {
        match request {
            channel::Request::BlogAdd(request) => handle_blog_add(&client, &request, rsp_tx).await,
            channel::Request::BlogUpdate(request) => {
                handle_blog_update(&client, &request, rsp_tx).await
            }
            channel::Request::BlogGet(id) => handle_blog_get(&client, id, rsp_tx).await,
            channel::Request::BlogsGet(request) => {
                handle_blogs_get(&client, &request, rsp_tx).await
            }
            channel::Request::BlogDelete(id) => handle_blog_delete(&client, id, rsp_tx).await,
            channel::Request::BlogViewInc(id) => handle_blog_view_inc(&client, id, rsp_tx).await,
            channel::Request::BlogStarInc(id) => handle_blog_star_inc(&client, id, rsp_tx).await,
            channel::Request::BlogStarDec(id) => handle_blog_star_dec(&client, id, rsp_tx).await,

            channel::Request::IdeaAdd(request) => handle_idea_add(&client, &request, rsp_tx).await,
            channel::Request::IdeaUpdate(request) => {
                handle_idea_update(&client, &request, rsp_tx).await
            }
            channel::Request::IdeaGet(id) => handle_idea_get(&client, id, rsp_tx).await,
            channel::Request::IdeasGet(request) => {
                handle_ideas_get(&client, &request, rsp_tx).await
            }
            channel::Request::IdeaDelete(id) => handle_idea_delete(&client, id, rsp_tx).await,
            channel::Request::IdeaViewInc(id) => handle_idea_view_inc(&client, id, rsp_tx).await,
            channel::Request::IdeaStarInc(id) => handle_idea_star_inc(&client, id, rsp_tx).await,
            channel::Request::IdeaStarDec(id) => handle_idea_star_dec(&client, id, rsp_tx).await,

            _ => panic!("Invalid channel request type"),
        }
    }
}
